package cn.com.dashihui.web.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.IAtom;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.web.dao.SpikeList;

public class SpikeListService {

	public Page<Record> findByPage(int pageNum, int pageSize, String title) {
		StringBuffer sbf = new StringBuffer("FROM t_spike_list");
		List<Object> params = new ArrayList<Object>();
		if (StrKit.notBlank(title)) {
			sbf.append(" WHERE active_title LIKE ?");
			params.add("%" + title + "%");
		}
		sbf.append(" ORDER BY id ASC");
		return Db.paginate(pageNum, pageSize, "SELECT * ", sbf.toString(), params.toArray());
	}

	/*
	 * public Page<Record> findList(int pageNum, int pageSize) { StringBuffer
	 * sbl = new StringBuffer(
	 * "FROM t_spike_list L LEFT JOIN t_spike_time T ON L.timeid=T.id LEFT JOIN t_bus_goods G ON G.id=L.goodsid "
	 * ); List<Object> list = new ArrayList<Object>(); sbl.append(
	 * "ORDER BY T.id DESC"); return Db.paginate(pageNum, pageSize,
	 * "SELECT T.id,G.name,G.goodsNum,G.sellPrice,G.sellpoint,G.stock,G.buycount,G.urv "
	 * , sbl.toString(), list.toArray());
	 * 
	 * }
	 */

	public Page<Record> findTimeList(int pageNum, int pageSize) {
		StringBuffer sb = new StringBuffer("FROM t_spike_list L, t_spike_time T WHERE L.timeid=T.id ");
		List<Object> params = new ArrayList<Object>();
		sb.append("ORDER BY T.id DESC");
		return Db.paginate(pageNum, pageSize, "SELECT T.id,T.time_line,T.begin,T.end ", sb.toString(),
				params.toArray());
	}

	public SpikeList findById(int id) {

		return SpikeList.me().findFirst("SELECT * FROM t_spike_list WHERE id=?", id);
	}

	public boolean editList(SpikeList object) {

		return object.update();
	}

	// 删除活动,同时要删除和活动列表相关联的中间表
	public boolean delList(int id) {
		// 事务处理，同时删除t_spike_list和t_spike_union表
		return Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				boolean retx = SpikeList.me().deleteById(id);
				Db.update("DELETE FROM t_spike_union WHERE listId=" + id);
				return retx;
			}
		});
	}

	public boolean sortList(Map<String, String> objMap) {
		int batchSize = objMap.size();
		List<String> sqlList = new ArrayList<String>();
		String strs = new String();
		String str = new String();
		String id = new String();
		for (Map.Entry<String, String> entry : objMap.entrySet()) {
			strs = entry.getKey();
			str = strs.substring(0, strs.lastIndexOf("/"));
			id = strs.substring(strs.indexOf("/") + 1);
			if (str.equals("spikeNum")) {
				sqlList.add("UPDATE t_spike_union SET " + str + "=" + entry.getValue() + ", spikeLeft="
						+ entry.getValue() + " WHERE id=" + id);
				sqlList.add("UPDATE t_spike_union SET " + str + "=" + entry.getValue() + ", spikeRemind="
						+ entry.getValue() + " WHERE id=" + id);
			} else {
				sqlList.add("UPDATE t_spike_union SET " + str + "=" + entry.getValue() + " WHERE id=" + id);
			}
		}
		int[] result = Db.batch(sqlList, batchSize);
		return result.length > 0;

	}

	// 新增活动
	public boolean addSpikeList(SpikeList object) {

		return object.save();
	}

	// 删除活动内的商品
	public boolean delGoods(int id) {
		List<String> list = new ArrayList<String>();
		list.add("DELETE FROM t_spike_union WHERE id=" + id);
		Db.batch(list, list.size());
		return true;
	}

	public List<Record> findAllSpikeList() {
		return Db.find("SELECT * FROM t_spike_list");
	}

	// 分页查询当前活动内的商品列表
	public Page<Record> findGoodsByPage(int pageNumber, int pageSize, int id) {

		StringBuffer sb = new StringBuffer(
				"FROM t_spike_union U, t_bus_goods G WHERE U.goodsId=G.id AND U.listId=" + id+" AND G.groupbuying=1 AND G.state=2 ");
		List<Object> params = new ArrayList<Object>();
		sb.append(" ORDER BY U.orderNo DESC");
		return Db.paginate(pageNumber, pageSize,
				"SELECT U.id,G.name,G.goodsNum,G.sellPrice,U.spikePrice,U.spikeNum,U.spikeRemind,U.spikeLeft,U.orderNo,U.limited ",
				sb.toString(), params.toArray());
	}

	// 分页查询不包含在当前活动内的商品
	public Page<Record> findOutSideGoodsByPage(int pageNum, int pageSize, int id, String title) {
		StringBuffer s = new StringBuffer(
				"FROM t_bus_goods G WHERE G.id NOT IN (SELECT goodsId FROM t_spike_union WHERE listId= " + id + ")  AND G.groupbuying=1 AND G.state=2");
		List<Object> param = new ArrayList<Object>();
		if (!StrKit.isBlank(title)) {
			/* s.append(" AND G.name LIKE ? OR G.goodsNum ="+title); */
			s.append(" AND G.name LIKE ?");
			param.add("%" + title + "%");
		}
		s.append(" ORDER BY G.id DESC");
		return Db.paginate(pageNum, pageSize, "SELECT G.id, G.name,G.goodsNum,G.sellPrice ", s.toString(),
				param.toArray());
	}

	// 活动列表添加商品
	public boolean addGoods(String[] checkcode, String gid) {
		int batchSize = checkcode.length;
		List<String> sqlList = new ArrayList<String>();
		for (String id : checkcode) {
			if((Db.queryLong("SELECT COUNT(goodsId) FROM t_spike_union WHERE listId=" + gid + " AND goodsId=" + id)==0)){
			sqlList.add("INSERT INTO t_spike_union (listId,goodsId) values(" + gid + "," + id + ")");}
		}
		int[] result = Db.batch(sqlList, batchSize);
		return result.length > 0;

	}

	/*public boolean findGoodsInGroup(String code, String id) {
		StringBuffer s = new StringBuffer(
				"SELECT COUNT(goodsId) FROM t_spike_union WHERE listId=" + id + " AND goodsId=" + code);
		int d=Db.queryInt(s.toString());
		return Db.queryBoolean(s.toString());
		

	}
*/
}
